package com.scm.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.scm.model.ScmUser;
import com.scm.dao.DBC;



public class OPscmUser
{
	private Connection conn=null;
	private PreparedStatement pp=null;
	private ResultSet rt=null;
	
	private boolean back=false;
	private String sql="";
	private int rowCount=0;
	private int pageSize=5;
	
	/**
	 * 得到account相应，scmuser信息
	 * @param username
	 * @return arraylist
	 */
	public ArrayList<ScmUser> getScmUser(String account){
		sql="select account,password,name,createdate,status from scmuser " +
				"where account=" +
				account;
		ArrayList<ScmUser> list=new ArrayList<ScmUser>();
		conn=new DBC().getConn();
		try
		{
			pp=conn.prepareStatement(sql);
			rt=pp.executeQuery();
			if(rt.next()){
				ScmUser oeo=new ScmUser();
				 oeo.setAccount(rt.getString("account"));
				 oeo.setPassword(rt.getString("password"));
				 oeo.setName(rt.getString("name"));
				 oeo.setCreatedate(rt.getString("createdate"));
				 oeo.setStatus(rt.getString("status"));
				 list.add(oeo);
			}
		}
		catch (SQLException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		close();
		return list;
	}
	/**
	 * 得到所有scmuser信息,
	 * @param username
	 * @return arraylist
	 */
	public ArrayList<ScmUser> getScmUser(){
		sql="select account,password,name,createdate,status from scmuser ";
		ArrayList<ScmUser> list=new ArrayList<ScmUser>();
		conn=new DBC().getConn();
		try
		{
			pp=conn.prepareStatement(sql);
			rt=pp.executeQuery();
			if(rt.next()){
				ScmUser oeo=new ScmUser();
				 oeo.setAccount(rt.getString("account"));
				 oeo.setPassword(rt.getString("password"));
				 oeo.setName(rt.getString("name"));
				 oeo.setCreatedate(rt.getString("createdate"));
				 oeo.setStatus(rt.getString("status"));
				 list.add(oeo);
			}
		}
		catch (SQLException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		close();
		return list;
	}
	/**
	 * scmuser的分页
	 * @param p pageNow pageCount 的索引 0 1
	 * @return
	 */
	public ArrayList<ScmUser> getResultset(int[] p) {

		ArrayList<ScmUser> al = new ArrayList<ScmUser>();
		
		try {

			// p[0]--pageNow
			// p[1]--pageCount
			sql="select count(*) from scmuser";
			//get connection
			conn=new DBC().getConn();
			// 3 creat Statement
			pp = conn.prepareStatement(sql);
			// 4 query 总记录数
			rt = pp.executeQuery();
			if (rt.next()) {
				rowCount = rt.getInt(1);
			}

			// calculate pageCount
			if (rowCount % pageSize == 0) {
				p[1] = rowCount / pageSize;
			} else {
				p[1] = rowCount / pageSize + 1;
			}

			// 查询显示的记录
			rt = pp.executeQuery("select account,password,name,createdate,status " 
					+"from scmuser limit "
					+ pageSize* (p[0] - 1) + "," + pageSize);
			
			//resultSet copy to ArrayList
			while(rt.next()){
				ScmUser sub=new ScmUser();
				sub.setAccount(rt.getString(1));
				sub.setPassword(rt.getString(2));
				sub.setName(rt.getString(3));
				sub.setCreatedate(rt.getString(4));
				sub.setStatus(rt.getString(5));
				//添加到arraylist 必须滴
				al.add(sub);
			}

		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			close();
		}

		return al;
	}
	/**
	 * 删除一条用户，按account
	 * @param id就是用户account
	 * @return true OK
	 */
	public boolean delUserById(String id){
		
		try {
			sql="delete from scmuser where account='"+ id+ "'";
			// 2 conncet
			conn = new DBC().getConn();
			// 3 creat Statement
			pp = conn.prepareStatement(sql);
			// 4 query
			int a =pp.executeUpdate();
			if(a==1)
				back=true;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			// 关闭打开的资源
			close();
		}

		return back;
	}
	/**
	 * 修改用户信息，按account
	 * @param account
	 * @return True is OK
	 */
	public boolean modById(ScmUser scmUser){
		
		try {
			sql="update  scmuser set password='" +scmUser.getPassword()+"'," +
				"set name='" +scmUser.getName()+"',"+
				"set status='" +scmUser.getStatus() +"' " +
				"where account='"+ scmUser.getAccount()+ "'";
			// 2 conncet
			conn = new DBC().getConn();
			// 3 creat Statement
			pp = conn.prepareStatement(sql);
			// 4 query
			int a =pp.executeUpdate();
			if(a==1)
				back=true;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			// 关闭打开的资源
			close();
		}

		return back;
	}
	/**
	 * 添加一条用户
	 * @param scmUser
	 * @return true is OK
	 */
	public boolean add(ScmUser scmUser){
		
		try {
			sql="insert into scmuser values('" +
					scmUser.getAccount()+"','"+
					scmUser.getPassword()+"','"+
					scmUser.getName()+"','"+
					scmUser.getCreatedate()+"','"+
					scmUser.getStatus()+"')";
			// 2 conncet
			conn = new DBC().getConn();
			// 3 creat Statement
			pp = conn.prepareStatement(sql);
			// 4 query
			int a =pp.executeUpdate();
			if(a==1)
				back=true;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			// 关闭打开的资源
			close();
		}

		return back;
	}
	/**
	 * 关闭数据库链接
	 */
	private void close() {

		try {
			if (rt != null) {
				rt.close();
				rt = null;
			}
			if (pp != null) {
				pp.close();
				pp = null;
			}
			if (conn != null) {
				conn.close();
				conn = null;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}
}
